import type { Router } from "express"
import type { Ioracle,IcallbackFn } from "../../types"

const carSummer = (router: Router, oracle: Ioracle, callbackFn: IcallbackFn) => {
  router.post("/getCarSetup", (_req, res) => {

    let sql = "select a.header_id,a.line_name,a.car_license,a.seat_num,b.site_name "
    sql += " ,b.start_time,b.end_time "
    sql += " ,row_number() over(partition by b.header_id order by b.start_time) seq "
    sql += " ,count(*)over(partition by a.header_id) cnt1 "
    sql += " ,count(distinct a.header_id)over(partition by 1) cnt2 "
    sql += " ,(select count(*) cnt from xx_general_car_passenger x,personnel_basic c where x.date_type = '工作日' and x.work_num = c.no and c.resign_date is null and x.site_name = b.site_name and nvl(status,'Y') = 'Y') cnt3 "
    sql += " ,(select count(*) cnt from xx_general_car_apply_header y,personnel_basic c where y.apply_type = '申請乘車' and y.apply_num = c.no and c.resign_date is null and y.apply_site = b.site_name and y.flow_phase = '待安排乘車') cnt4 "
    sql += " from xx_general_car_setup a,xx_general_car_setup_detail b "
    sql += " where a.header_id = b.header_id  "
    sql += " and a.car_type = 'A' "
    sql += " order by a.line_name,b.start_time "

    callbackFn( res, oracle.connChaos,{sql})

  })

  return router
}

export default carSummer